| 排查运行缓慢的查询问题 | 您所在的位置：网站首页 › sql server 连表查询 › 排查运行缓慢的查询问题 |
|
排查SQL Server上运行缓慢的查询问题
项目
06/20/2023
原始产品版本：SQL Server 原始 KB 编号： 243589 简介本文介绍如何处理数据库应用程序在使用SQL Server时可能会遇到的性能问题：特定查询或查询组的性能缓慢。 以下方法将帮助你缩小查询速度缓慢问题的原因范围，并引导你解决问题。 查找慢速查询若要确定SQL Server实例存在查询性能问题，请首先按查询的执行时间 (运行时间) 检查查询。 根据建立的性能基线，检查时间是否超过 () 设置的阈值（以毫秒为单位）。 例如，在压力测试环境中，你可能已为工作负荷设置了不超过 300 毫秒的阈值，并且可以使用此阈值。 然后，可以识别超过该阈值的所有查询，重点关注每个查询及其预先建立的性能基线持续时间。 最终，业务用户关心数据库查询的总体持续时间：因此，主要侧重于执行持续时间。 收集 CPU 时间和逻辑读取等其他指标，以帮助缩小调查范围。 对于当前正在执行的语句，请检查sys.dm_exec_requests中的total_elapsed_time和cpu_time列。 运行以下查询以获取数据： SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC;对于过去执行的查询，请检查sys.dm_exec_query_stats中的last_elapsed_time列和last_worker_time列。 运行以下查询以获取数据： SELECT t.text, (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time, (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time, ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE t.text like '%' -- Replace with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC注意 如果 avg_wait_time 显示负值，则为 并行查询。 如果可以在 SQL Server Management Studio (SSMS) 或 Azure Data Studio 中按需执行查询，请使用 SET STATISTICS TIMEON 和 SET STATISTICS IOON 运行它。 SET STATISTICS TIME ON SET STATISTICS IO ON SET STATISTICS IO OFF SET STATISTICS TIME OFF然后，从 “消息”中，你将看到 CPU 时间、运行时间和逻辑读取，如下所示： Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 460 ms, elapsed time = 470 ms.如果可以收集查询计划，请检查 执行计划属性中的数据。 运行包含 实际执行计划的 查询。 从 执行计划中选择最左侧的运算符。 在 “属性”中，展开 “QueryTimeStats 属性”。 选中 “已用时间” 和 “CpuTime”。 正在运行与等待：为什么查询速度较慢？ 如果发现超过预定义阈值的查询，请检查它们速度缓慢的原因。 性能问题的原因可分为两类：正在运行或正在等待： 正在等待：查询可能会很慢，因为它们等待瓶颈的时间很长。 请参阅 等待类型中瓶颈的详细列表。 正在运行：查询可能很慢，因为它们长时间运行 (执行) 。 换句话说，这些查询主动使用 CPU 资源。 查询可以运行一段时间，并在其生存期内等待一段时间， (持续时间) 。 但是，你的重点是确定哪个类别是导致其长时间运行的主要类别。 因此，第一个任务是确定查询属于哪个类别。 很简单：如果查询未运行，则它正在等待。 理想情况下，查询的大部分运行时间都处于运行状态，而等待资源的时间很少。 此外，在最佳情况下，查询在预先确定的基线内或以下运行。 比较查询的运行时间和 CPU 时间，以确定问题类型。 类型 1：CPU 绑定 (运行器)如果 CPU 时间接近、等于或高于已用时间，则可以将其视为 CPU 绑定查询。 例如，如果运行时间为 3000 毫秒 (毫秒) 且 CPU 时间为 2900 毫秒，则表示大部分运行时间都花在 CPU 上。 然后，我们可以说这是一个受 CPU 限制的查询。 运行 (CPU 绑定) 查询的示例： 运行时间 (ms) cpu time (ms) 读取 (逻辑) 3200 3000 300000 1080 1000 20逻辑读取（读取缓存中的数据/索引页）是SQL Server中 CPU 使用率的驱动因素。 在某些情况下，CPU 使用可能来自其他源：T-SQL 中的 while 循环 (或其他代码（如 XProcs 或 SQL CRL 对象) ）。 表中的第二个示例演示了这样的方案，其中大部分 CPU 不是来自读取。 注意 如果 CPU 时间大于持续时间，则表示执行并行查询;多个线程同时使用 CPU。 有关详细信息，请参阅 并行查询 - 运行器或等待者。 类型 2：等待瓶颈 (服务员)如果已用时间明显大于 CPU 时间，则查询正在等待瓶颈。 运行时间包括在 CPU 上执行查询的时间 (CPU 时间) ，以及等待释放资源的时间 (等待时间) 。 例如，如果运行时间为 2000 毫秒，CPU 时间为 300 毫秒，则等待时间为 1700 毫秒 (2000 - 300 = 1700) 。 有关详细信息，请参阅 等待类型。 等待查询的示例： 运行时间 (ms) cpu time (ms) 读取 (逻辑) 2000 300 28000 10080 700 80000 并行查询 - 运行器或服务员并行查询使用的 CPU 时间可能比总持续时间多。 并行度的目标是允许多个线程同时运行查询的一部分。 在时钟时间的一秒内，查询可以通过执行 8 个并行线程来使用 8 秒的 CPU 时间。 因此，根据已用时间和 CPU 时间差确定受 CPU 限制或等待的查询具有挑战性。 但是，作为一般规则，请遵循上述两节中列出的原则。 摘要如下： 如果已用时间远远大于 CPU 时间，则将其视为服务员。 如果 CPU 时间远远大于已用时间，则将其视为运行器。并行查询的示例： 运行时间 (ms) cpu time (ms) 读取 (逻辑) 1200 8100 850000 3080 12300 1500000 方法的高级视觉表示形式诊断并解决等待的查询 如果确定感兴趣的查询是服务员，则下一步是专注于解决瓶颈问题。 否则，请转到步骤 4： 诊断和解决正在运行的查询。 若要优化等待瓶颈的查询，请确定等待时间以及瓶颈 (等待类型) 的位置。 确认 等待类型 后，请减少等待时间或完全消除等待。 若要计算近似等待时间，请从查询的运行时间中减去 CPU 时间 (辅助角色时间) 。 通常，CPU 时间是实际执行时间，查询生存期的剩余部分正在等待。 有关如何计算近似等待持续时间的示例： 运行时间 (ms) cpu time (ms) 等待时间 (ms) 3200 3000 200 7080 1000 6080 确定瓶颈或等待若要识别历史长时间等待查询 (例如， >总运行时间的 20% 是等待时间) ，请运行以下查询。 此查询使用自SQL Server开始以来缓存查询计划的性能统计信息。 SELECT t.text, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_worker_time / qs.execution_count AS avg_cpu_time, (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, qs.total_elapsed_time AS cumulative_elapsed_time FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 0.2 ORDER BY qs.total_elapsed_time / qs.execution_count DESC若要确定等待时间超过 500 毫秒的当前正在执行的查询，请运行以下查询： SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE wait_time > 500 AND is_user_process = 1如果可以收集查询计划，请在 SSMS 中的执行计划属性中检查 WaitStats： 运行包含 实际执行 计划的查询。 右键单击“ 执行计划 ”选项卡中最左侧的运算符 选择 “属性” ，然后选择 “WaitStats 属性”。 检查 WaitTimeMs 和 WaitType。如果熟悉 PSSDiag/SQLdiag 或 SQL LogScout LightPerf/GeneralPerf 方案，请考虑使用其中任一方案来收集性能统计信息，并确定SQL Server实例上的等待查询。 可以导入收集的数据文件，并使用 SQL Nexus 分析性能数据。 帮助消除或减少等待的引用每种等待类型的原因和解决方法各不相同。 没有一种常规方法可以解决所有等待类型。 下面是排查和解决常见等待类型问题的文章： 了解并解决 (LCK_M_*) 阻塞性问题 了解并解决Azure SQL数据库阻塞问题 排查 I/O 问题导致的SQL Server性能缓慢 (PAGEIOLATCH_*、WRITELOG、IO_COMPLETION、BACKUPIO) WRITELOG 等待和常见原因 解决SQL Server中最后一页插入PAGELATCH_EX争用问题 内存授予解释和解决方案 (RESOURCE_SEMAPHORE) 排查ASYNC_NETWORK_IO等待类型导致查询速度缓慢的问题 排查Always On可用性组的高HADR_SYNC_COMMIT等待类型问题 工作原理：CMEMTHREAD 和调试 使并行度等待可操作 THREADPOOL 等待有关许多 Wait 类型的说明及其指示的内容，请参阅 “等待类型”中的表。 诊断并解决正在运行的查询如果 CPU (辅助角色) 时间非常接近总运行持续时间，则查询将花费大部分生存期执行。 通常，当SQL Server引擎导致 CPU 使用率过高时，CPU 使用率过高来自驱动大量逻辑读取的查询， (最常见的原因) 。 若要确定当前负责高 CPU 活动的查询，请运行以下语句： SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC如果查询目前未驱动 CPU，可以运行以下语句来查找历史占用大量 CPU 的查询： SELECT TOP 10 st.text AS batch_text, SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text, (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms, (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms, (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st ORDER BY(qs.total_worker_time / qs.execution_count) DESC 用于解决长时间运行的 CPU 受限查询的常见方法 检查查询的查询计划 更新统计信息 标识并应用 缺少的索引。 有关如何识别缺失索引的更多步骤，请参阅 优化缺少索引的非聚集索引建议 重新设计或重写查询 识别和解析 参数敏感型计划 识别并解决 SARG 能力问题 识别并解决 以下行目标 问题：TOP、EXISTS、IN、FAST、SET ROWCOUNT、OPTION (FAST N) 会导致长时间运行的嵌套循环。 有关详细信息，请参阅 行目标消失流氓 和 显示计划增强功能 - 行目标 EstimateRowsWithoutRowGoal 评估和解决 基数估计 问题。 有关详细信息，请参阅从 SQL Server 2012 或更早版本升级到 2014 或更高版本后降低的查询性能 识别并解决似乎从未完成的查询，请参阅排查似乎永远不会结束的查询SQL Server 识别并解决 受优化器超时影响的慢查询 确定 CPU 性能过高的问题。 有关详细信息，请参阅排查SQL Server中的 CPU 使用率过高问题 排查显示两个服务器之间显著性能差异的查询 增加系统 (CPU) 上的计算资源 排查狭窄和较宽计划的 UPDATE 性能问题 建议的资源 SQL Server和Azure SQL 托管实例中可检测到的查询性能瓶颈类型 性能监视和优化工具 SQL Server中的自动优化选项 索引体系结构和设计指南 排查查询超时错误 解决 SQL Server 中 CPU 使用率过高的问题 从 SQL Server 2012 或更早版本升级到 2014 或更高版本后，查询性能下降 |
| CopyRight 2018-2019 实验室设备网 版权所有 |